﻿Imports System.Data.SqlClient
Imports System.Data
Imports System.Data.Common
Imports System.IO
Imports System.Security.Cryptography
Imports System.Drawing

Public Class DBManager
    'This class is to manage all the database functions

    'Connection Strings


    'Private Shared strConnectionID As String = "Data Source=.\sqlmr2;Initial Catalog=goodAnotDB;Integrated Security=True"
    'Private Shared strConnectionID As String = "Data Source= JOLENE-PC\;Initial Catalog=goodAnotDB;Integrated Security=True"
    Private Shared strConnectionID As String = "Data Source= Yijun-PC\;Initial Catalog=goodAnotDB;Integrated Security=True"


#Region "Authentication and Authorization module"

    'Login using email and password
    Public Function login(ByVal email As String, ByVal password As String) As Boolean

        Dim result As Boolean = True
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "select * from user_account where email = @email and password = @password"

            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@email", email)
            sqlCmd.Parameters.AddWithValue("@password", password)

            dr = sqlCmd.ExecuteReader

            'If there is rows returned, it means that login successful
            If dr.HasRows Then
                result = True
            Else
                result = False
            End If

            sqlConn.Close()
        Catch ex As Exception
            result = False
        End Try

        'True = no errors
        'False = exceptions occured
        Return result
    End Function

    'If login successful, get user details
    Public Function getUserDetails(ByVal email As String) As userAcc
        Dim userLoggedIn As userAcc = Nothing
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "select user_ID, display_name, verified, is_admin, date_created, super_admin from user_account where email = @email"
            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@email", email)
            dr = sqlCmd.ExecuteReader

            'Retrieving data
            While dr.Read()
                Dim userID = Convert.ToInt32(dr(0).ToString())
                Dim displayName = dr(1).ToString()
                Dim verified = Convert.ToBoolean(dr(2).ToString())
                Dim isAdmin = Convert.ToBoolean(dr(3).ToString())
                Dim dateCreated = Convert.ToDateTime(dr(4).ToString()).Date
                Dim superAdmin = Convert.ToBoolean(dr(5).ToString())
                userLoggedIn = New userAcc(userID, displayName, verified, isAdmin, dateCreated, superAdmin)
            End While

            sqlConn.Close()
        Catch ex As Exception

        End Try
        Return userLoggedIn



    End Function

    'Get the number of reviews from specified user
    Public Function getNoReviews(ByVal userID As Integer) As Integer
        Dim numReviews As Integer
        Dim result As Boolean = True
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "select reviews_no from user_account where user_ID = @userID"

            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@user_ID", userID)

            dr = sqlCmd.ExecuteReader

            'Retrieving data
            While dr.Read()
                numReviews = Convert.ToInt32(dr(0).ToString())
            End While

            sqlConn.Close()
        Catch ex As Exception
            'IF exception occured, set as -1
            numReviews = -1
        End Try

        'True = no errors
        'False = exceptions occured
        Return numReviews
    End Function

    'Check verification code
    Public Function verify(ByVal userID As Integer, ByVal verificationCode As String) As Boolean
        Dim result As Boolean = True
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "select * from user_validation where user_ID = @userID and validation_code = @validationCode"

            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@userID", userID)
            sqlCmd.Parameters.AddWithValue("@validationCode", verificationCode)
            dr = sqlCmd.ExecuteReader
            'If there is rows returned, it means that login successful
            If dr.HasRows Then
                result = True

                'If successful, call verifyUpdate method
                verifyUpdate(userID)
            Else
                result = False
            End If

            sqlConn.Close()
        Catch ex As Exception
            result = False
        End Try

        'True = no errors
        'False = exceptions occured
        Return result
    End Function

    'If verification succeeds, update user_account table and delete verification code from user_validation table
    Public Sub verifyUpdate(ByVal userID As Integer)
        Dim result As Boolean = True

        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'First query to update user details
            sqlCmd.CommandText = "Update user_account set verified = @true where user_ID = @userID"
            sqlCmd.Parameters.AddWithValue("@true", "True")
            sqlCmd.Parameters.AddWithValue("@userID", userID)
            sqlCmd.ExecuteNonQuery()

            'Second query to delete from validation
            sqlCmd.CommandText = "Delete from user_validation where user_ID = @userID"
            sqlCmd.ExecuteNonQuery()
            sqlConn.Close()

        Catch ex As Exception
        End Try

    End Sub
#End Region

#Region "User module"

    'Add user
    Public Function addUser(ByVal email As String, ByVal password As String, ByVal displayName As String, ByVal gender As Char) As Integer

        Dim result As Integer = 0
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "Insert into user_account( email , password , display_name, gender, verified, is_admin, date_created, reviews_no, super_admin) Values ( @email , @password , @display_name, @gender, @verified, @is_admin, @date_created, @reviews_no, @super_admin); SELECT SCOPE_IDENTITY()"

            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@email", email.ToLower())
            sqlCmd.Parameters.AddWithValue("@password", password)
            sqlCmd.Parameters.AddWithValue("@display_name", displayName)
            sqlCmd.Parameters.AddWithValue("@gender", gender)
            sqlCmd.Parameters.AddWithValue("@verified", "false")
            sqlCmd.Parameters.AddWithValue("@is_admin", "false")
            sqlCmd.Parameters.AddWithValue("@date_created", Date.Today.Date)
            sqlCmd.Parameters.AddWithValue("@reviews_no", 0)
            sqlCmd.Parameters.AddWithValue("@super_admin", "false")
            result = sqlCmd.ExecuteScalar
            sqlConn.Close()
        Catch ex As Exception
        End Try

        'True = no errors
        'False = exceptions occured
        Return result
    End Function

    'Retrieve user
    Public Function retrieveUser(ByVal display_name As String) As userAcc
        Dim user As userAcc = Nothing
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "Select email, password, first_name, last_name, gender, date_of_birth, country, verified, date_created from user_account where display_name= @display_name"
            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@display_name", display_name)
            dr = sqlCmd.ExecuteReader
            While dr.Read()
                Dim email = dr("email")
                Dim password = dr("password")
                Dim first_name = dr("first_name").ToString()
                Dim last_name = dr("last_name").ToString()
                Dim gender = dr("gender")
                Dim date_of_birth As Date
                Try
                    date_of_birth = dr("date_of_birth")
                Catch ex As Exception
                    date_of_birth = "01/01/0001"
                End Try
                Dim country = dr("country").ToString()
                Dim verified = dr("verified")
                Dim date_created = dr("date_created")
                user = New userAcc(display_name, password, verified, date_created, first_name, last_name, gender, email, date_of_birth, country)
            End While
            sqlConn.Close()
        Catch ex As Exception

        End Try
        'True = no errors
        'False = exceptions occured
        Return user
    End Function

    'Delete User
    Public Function deleteUser(ByVal display_name)
        Dim result As Boolean = True
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "Delete from user_account where display_name = @display_name"
            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@display_name", display_name)
            If sqlCmd.ExecuteNonQuery() > 0 Then
                result = True
            Else
                result = False
            End If

            sqlConn.Close()
        Catch ex As Exception
            result = False
        End Try

        'True = no errors
        'False = exceptions occured
        Return result
    End Function

    'Update User
    Public Function updateUser(ByVal email As String, ByVal display_name As String, ByVal first_name As String, ByVal last_name As String, ByVal date_of_birth As Date, ByVal country As String) As Boolean

        Dim dob As String
        If (date_of_birth.Date.ToShortDateString = "1/1/1753 AM 12:00:00") Then
            dob = ""
        Else
            dob = date_of_birth.Date.ToShortDateString
        End If

        Dim result As Boolean = True
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "Update user_account set display_name = @display_name, first_name = @first_name, last_name = @last_name, date_of_birth= @date_of_birth , country=@country where email = @email"

            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@email", email.ToLower())
            sqlCmd.Parameters.AddWithValue("@display_name", display_name)
            sqlCmd.Parameters.AddWithValue("@first_name", first_name)
            sqlCmd.Parameters.AddWithValue("@last_name", last_name)
            sqlCmd.Parameters.AddWithValue("@date_of_birth", date_of_birth)
            sqlCmd.Parameters.AddWithValue("@country", country)
            If sqlCmd.ExecuteNonQuery() > 0 Then
                result = True
            Else
                result = False
            End If

            sqlConn.Close()
        Catch ex As Exception
            result = False
        End Try

        'True = no errors
        'False = exceptions occured
        Return result
    End Function

    'Retrieve User ID
    Public Function retrieveUserID(ByVal display_name As String)
        Dim user_ID As String
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "Select user_ID from user_account where display_name= @display_name"
            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@display_name", display_name)
            dr = sqlCmd.ExecuteReader
            While dr.Read()
                user_ID = dr("user_ID")
            End While
            sqlConn.Close()
        Catch ex As Exception
        End Try
        'True = no errors
        'False = exceptions occured
        Return user_ID
    End Function

    'Change Password
    Public Function changePassword(ByVal display_name As String, ByVal password As String)

        Dim result As Boolean = True
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "Update user_account set password = @password where display_name = @display_name"

            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@display_name", display_name)
            sqlCmd.Parameters.AddWithValue("@password", password)
            If sqlCmd.ExecuteNonQuery() > 0 Then
                result = True
            Else
                result = False
            End If

            sqlConn.Close()
        Catch ex As Exception
            result = False
        End Try

        'True = no errors
        'False = exceptions occured
        Return result
    End Function

    'Check if email already exists
    Public Function checkAvailEmail(ByVal email As String)

        Dim result As Boolean = True
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "select * from user_account where email = @email"

            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@email", email)
            dr = sqlCmd.ExecuteReader
            'If there is rows returned, it means that name already exists
            If dr.HasRows Then
                result = False
            End If

            sqlConn.Close()
        Catch ex As Exception
            result = False
        End Try

        Return result
        'True = available
        'False = unavailable
    End Function

    'Check if display name already exists
    Public Function checkAvailDisplayName(ByVal displayName As String)

        Dim result As Boolean = True
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "select * from user_account where display_name = @displayName"

            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@displayName", displayName)
            dr = sqlCmd.ExecuteReader
            'If there is rows returned, it means that name already exists
            If dr.HasRows Then
                result = False
            End If

            sqlConn.Close()
        Catch ex As Exception
            result = False
        End Try

        'True = available
        'False = unavailable
        Return result
    End Function

    'Add report to admin dashboard
    Public Function addReportDashboard(ByVal priority As Integer, ByVal type As String, ByVal comment As String, ByVal ID As Integer, ByVal resolved As Integer)

        Dim result As Boolean = True
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "Insert into dashboard_report( priority, type, comment, ID, resolved ) Values ( @priority, @type, @comment, @ID, @resolved )"

            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@priority", priority)
            sqlCmd.Parameters.AddWithValue("@type", type)
            sqlCmd.Parameters.AddWithValue("@comment", comment)
            sqlCmd.Parameters.AddWithValue("@ID", ID)
            sqlCmd.Parameters.AddWithValue("@resolved", 0)

            If sqlCmd.ExecuteNonQuery() > 0 Then
                result = True
            Else
                result = False
            End If
            sqlConn.Close()
        Catch ex As Exception
            result = False
        End Try

        'True = no errors
        'False = exceptions occured
        Return result
    End Function

    'Email verification
    Public Function emailVerification(ByVal validation_code As String, ByVal userID As Integer)

        Dim result As Boolean = True
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "Insert into user_validation(validation_code, user_ID, date) Values (@validation_code, @userID, @date)"

            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@validation_code", validation_code)
            sqlCmd.Parameters.AddWithValue("@userID", userID)
            sqlCmd.Parameters.AddWithValue("@date", Date.Today.Date)

            If sqlCmd.ExecuteNonQuery() > 0 Then
                result = True
            Else
                result = False
            End If
            sqlConn.Close()
        Catch ex As Exception
            result = False
        End Try

        'True = no errors
        'False = exceptions occured
        Return result
    End Function

    'Verify User
    Public Function verifyUser(ByVal display_name As String, ByVal verified As String)
        Dim result As Boolean = True
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "Update user_account set verified = @verified where display_name = @display_name"

            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@display_name", display_name)
            sqlCmd.Parameters.AddWithValue("@verified", verified)
            If sqlCmd.ExecuteNonQuery() > 0 Then
                result = True
            Else
                result = False
            End If

            sqlConn.Close()
        Catch ex As Exception
            result = False
        End Try

        'True = no errors
        'False = exceptions occured
        Return result
    End Function

    'Hash Password
    Public Function hashPassword(ByVal password As String) As String
        'create our SHA1 provider
        Dim sha As SHA1 = New SHA1CryptoServiceProvider()
        Dim hashedValue As String = String.Empty
        'hash the data
        Dim hashedData As Byte() = sha.ComputeHash(Encoding.Unicode.GetBytes(password))

        'loop through each byte in the byte array
        For Each b As Byte In hashedData
            'convert each byte and append
            hashedValue += String.Format("{0,2:X2}", b)
        Next
        'return the hashed value
        Return hashedValue
    End Function

    'Forget Password
    Public Function forgetPassword(ByVal email As String, ByVal password As String)
        Dim result As Boolean = True
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "Update user_account set password = @password where email = @email"

            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@email", email)
            sqlCmd.Parameters.AddWithValue("@password", password)
            If sqlCmd.ExecuteNonQuery() > 0 Then
                result = True
            Else
                result = False
            End If

            sqlConn.Close()
        Catch ex As Exception
            result = False
        End Try

        'True = no errors
        'False = exceptions occured
        Return result
    End Function

#End Region

#Region "Shop Module"

    ' Add business details
    Public Function addBusinessDetails(ByVal name As String, ByVal category As String, ByVal website As String, ByVal details As String, ByVal date_created As DateTime, ByVal visits As Integer) As Integer
        Dim businessID As Integer = 0
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "Insert into Business(name ,category, website, details, date_created, visits) Values ( @name ,  @category, @website, @details, @date_created, @visits); SELECT SCOPE_IDENTITY()"

            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@name", name)
            sqlCmd.Parameters.AddWithValue("@category", category)
            sqlCmd.Parameters.AddWithValue("@website", website)
            sqlCmd.Parameters.AddWithValue("@details", details)
            sqlCmd.Parameters.AddWithValue("@date_created", DateTime.Now)
            sqlCmd.Parameters.AddWithValue("@visits", 0)
            businessID = sqlCmd.ExecuteScalar()
            sqlConn.Close()
        Catch ex As Exception
        End Try

        'True = no errors
        'False = exceptions occured
        Return businessID
    End Function

    ' Add outlet details
    Public Function getNumOutlets(ByVal outlet_ID) As Boolean

        Dim result As Boolean = True
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "select count(outlet_ID) from outlet where business_ID = (select business_ID from outlet where outlet_ID = @outlet_ID)"

            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@outlet_ID", outlet_ID)

            If sqlCmd.ExecuteNonQuery() > 0 Then
                result = True
            Else
                result = False
            End If
            sqlConn.Close()
        Catch ex As Exception
            result = False
        End Try

        'True = no errors
        'False = exceptions occured
        Return result
    End Function

    ' Add outlet details
    Public Function addOutletDetails(ByVal business_ID As Integer, ByVal name As String, ByVal address As String, ByVal openingHours As String, ByVal remarks As String, ByVal priority As Integer, ByVal phone_no As String, ByVal date_created As Date) As Boolean

        Dim result As Boolean = True
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "Insert into outlet(business_ID ,name , address , opening_hours, remarks, priority , phone_no, date_created) Values (@business_ID, @name , @address , @opening_hours, @remarks, @priority, @phone_no, @date_created)"

            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@business_ID", business_ID)
            sqlCmd.Parameters.AddWithValue("@name", name)
            sqlCmd.Parameters.AddWithValue("@address", address)
            sqlCmd.Parameters.AddWithValue("@opening_hours", openingHours)
            sqlCmd.Parameters.AddWithValue("@remarks", remarks)
            sqlCmd.Parameters.AddWithValue("@priority", priority)
            sqlCmd.Parameters.AddWithValue("@phone_no", phone_no)
            sqlCmd.Parameters.AddWithValue("@date_created", Today.Date)
            If sqlCmd.ExecuteNonQuery() > 0 Then
                result = True
            Else
                result = False
            End If
            sqlConn.Close()
        Catch ex As Exception
            result = False
        End Try

        'True = no errors
        'False = exceptions occured
        Return result
    End Function

    '  Add Business Outlet Report 
    Public Function addOutletReport(ByVal priority As Integer, ByVal type As String, ByVal comment As String, ByVal ID As Integer, ByVal resolved As Integer) As Boolean

        Dim result As Boolean = True
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "Insert into dashboard_report(priority, type, comment, ID, resolved) Values (@priority,@type, @comment, @ID, @resolved)"

            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@priority", priority)
            sqlCmd.Parameters.AddWithValue("@type", type)
            sqlCmd.Parameters.AddWithValue("@comment", comment)
            sqlCmd.Parameters.AddWithValue("@ID", ID)
            sqlCmd.Parameters.AddWithValue("@resolved", resolved)
            If sqlCmd.ExecuteNonQuery() > 0 Then
                result = True
            Else
                result = False
            End If
            sqlConn.Close()
        Catch ex As Exception
            result = False
        End Try

        'True = no errors
        'False = exceptions occured
        Return result
    End Function

    'Get business details by outletID
    Public Function getBusinessDetails(ByVal outletID As Integer) As String()
        Dim result(3) As String
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "SELECT business.name, business.category, business.website, business.details FROM outlet INNER JOIN business ON outlet.business_ID = business.business_ID WHERE (outlet.outlet_ID = @outlet_ID) GROUP BY business.name, business.category, business.website, business.details"
            sqlCmd.Parameters.AddWithValue("@outlet_ID", outletID)
            dr = sqlCmd.ExecuteReader
            While dr.Read()
                'Name
                result(0) = dr(0).ToString()
                'Category
                result(1) = dr(1).ToString()
                'Website
                result(2) = dr(2).ToString()
                'Details
                result(3) = dr(3).ToString()

            End While

            sqlConn.Close()
        Catch ex As Exception

        End Try
        Return result
    End Function

    'Get business details by businessID
    Public Function getBusinessDetailsBID(ByVal businessID As Integer) As String()
        Dim result(3) As String
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "SELECT business.name, business.category, business.website, business.details FROM business where business_ID=@business_ID"
            sqlCmd.Parameters.AddWithValue("@business_ID", businessID)
            dr = sqlCmd.ExecuteReader
            While dr.Read()
                'Name
                result(0) = dr(0).ToString()
                'Category
                result(1) = dr(1).ToString()
                ' Website
                result(2) = dr(2).ToString()
                'Details
                result(3) = dr(3).ToString()

            End While

            sqlConn.Close()
        Catch ex As Exception

        End Try
        Return result
    End Function

    'Get outlet details
    Public Function getOutletDetails(ByVal outletID As Integer) As String()
        Dim result(4) As String
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "SELECT name, address, opening_hours, remarks, phone_no FROM outlet WHERE (outlet_ID = @outlet_ID)"
            sqlCmd.Parameters.AddWithValue("@outlet_ID", outletID)
            dr = sqlCmd.ExecuteReader
            While dr.Read()
                'Name
                result(0) = dr(0).ToString()
                'Address
                result(1) = dr(1).ToString()
                'Opening hours
                result(2) = dr(2).ToString()
                'Remarks
                result(3) = dr(3).ToString()
                'Phone number
                result(4) = dr(4).ToString()

            End While

            sqlConn.Close()
        Catch ex As Exception

        End Try
        Return result
    End Function

    'Get list of outlets belonging to a business
    Public Function getListOutlets(ByVal outletID As Integer) As ArrayList
        Dim result = New ArrayList()
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            'Getting all the outlets with the same business_ID as itself
            sqlCmd.CommandText = "SELECT outlet_ID, name FROM outlet WHERE (business_ID = (SELECT business_ID FROM outlet AS outlet_1 WHERE (outlet_ID = @outlet_ID)))"
            sqlCmd.Parameters.AddWithValue("@outlet_ID", outletID)
            dr = sqlCmd.ExecuteReader
            While dr.Read()
                'Add each line as an list item to populate the dropdownlist, text = outlet name and value = outlet_ID
                Dim item As ListItem = New ListItem(dr(1).ToString(), Convert.ToInt32(dr(0).ToString()))
                result.Add(item)
            End While

            sqlConn.Close()
        Catch ex As Exception

        End Try
        Return result
    End Function

    'Get the list of outlets belonging to a business by businessID
    Public Function getListOutletsBID(ByVal businessID As Integer) As ArrayList
        Dim result = New ArrayList()
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            'Getting all the outlets with the same business_ID as itself
            sqlCmd.CommandText = "SELECT name ,address , opening_hours, remarks ,phone_no FROM outlet INNER JOIN business ON outlet.business_ID = business.business_ID WHERE (outlet.businessID = @business_ID) "
            sqlCmd.Parameters.AddWithValue("@business_ID", businessID)
            dr = sqlCmd.ExecuteReader
            While dr.Read()
                'Add each line as an list item to populate the dropdownlist, text = outlet name and value = outlet_ID

                dr(0).ToString()
                ' Dim item As ListItem = New ListItem(dr(1).ToString(), Convert.ToInt32(dr(0).ToString()))
                'result.Add(item)
            End While

            sqlConn.Close()
        Catch ex As Exception

        End Try
        Return result
    End Function

    'update business details for admin 
    Public Function updateBusinessDetails(ByVal name As String, ByVal category As String, ByVal website As String, ByVal last_name As String, ByVal details As String, ByVal date_created As Date) As Boolean

        Dim result As Boolean = True
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "Insert business set name = @name, category = @category, website = @website, details = @details, date_created=@date_created"

            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@name", name)
            sqlCmd.Parameters.AddWithValue("@category", category)
            sqlCmd.Parameters.AddWithValue("@website", website)
            sqlCmd.Parameters.AddWithValue("@details", details)
            sqlCmd.Parameters.AddWithValue("@date_created", Today.Date)
            If sqlCmd.ExecuteNonQuery() > 0 Then
                result = True
            Else
                result = False
            End If

            sqlConn.Close()
        Catch ex As Exception
            result = False
        End Try

        'True = no errors
        'False = exceptions occured
        Return result
    End Function

    'update outlet details for admin 
    Public Function updateOutletDetails(ByVal outlet_ID As Integer, ByVal name As String, ByVal address As String, ByVal opening_hours As String, ByVal remarks As String, ByVal priority As Integer, ByVal phone_no As String, ByVal date_created As Date) As Boolean

        Dim result As Boolean = True
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "Update outlet set name = @name, address = @address, opening_hours = @opening_hours, remarks = @remarks, priority =@priority,date_created=@date_created where outlet_ID = @outlet_ID"

            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@name", name)
            sqlCmd.Parameters.AddWithValue("@address", address)
            sqlCmd.Parameters.AddWithValue("@opening_hours", opening_hours)
            sqlCmd.Parameters.AddWithValue("@remarks", remarks)
            sqlCmd.Parameters.AddWithValue("@priority", 2)
            sqlCmd.Parameters.AddWithValue("@date_created", Today.Date)
            sqlCmd.Parameters.AddWithValue("@outlet_ID", outlet_ID)
            If sqlCmd.ExecuteNonQuery() > 0 Then
                result = True
            Else
                result = False
            End If

            sqlConn.Close()
        Catch ex As Exception
            result = False
        End Try

        'True = no errors
        'False = exceptions occured
        Return result
    End Function

    'delete business details
    Public Function deleteBusinessDetails(ByVal businessID As Integer) As Boolean
        Dim result = False
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "select outlet_ID from outlet where business_ID = @businessID"
            sqlCmd.Parameters.AddWithValue("@businessID", Convert.ToInt32(businessID))
            dr = sqlCmd.ExecuteReader
            While dr.Read
                Me.deleteOutletDetails(dr(0))
            End While
            dr.Close()

            sqlCmd.CommandText = "delete from business WHERE (business_ID = @businessID)"
            sqlCmd.ExecuteNonQuery()

            sqlConn.Close()
        Catch ex As Exception
            result = False
        End Try
        Return result
    End Function

    'delete outlet details
    Public Function deleteOutletDetails(ByVal outletID As Integer) As Boolean
        Dim result = False
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "Select review_ID from review WHERE (outlet_ID = @outletID)"
            sqlCmd.Parameters.AddWithValue("@outletID", Convert.ToInt32(outletID))
            dr = sqlCmd.ExecuteReader
            While dr.Read
                Me.deleteReview(dr(0))
            End While
            dr.Close()


            sqlCmd.CommandText = "delete from outlet WHERE (outlet_ID = @outletID)"
            sqlCmd.ExecuteNonQuery()


            sqlConn.Close()
        Catch ex As Exception
            result = False
        End Try
        Return result
    End Function

    'get business name from outletID
    Public Function getBusinessName(ByVal outletID As Integer) As String
        Dim result As String = ""
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "SELECT business.name FROM outlet INNER JOIN business ON outlet.business_ID = business.business_ID WHERE (outlet.outlet_ID = @outlet_ID) GROUP BY business.name"
            sqlCmd.Parameters.AddWithValue("@outlet_ID", outletID)
            dr = sqlCmd.ExecuteReader
            While dr.Read()
                result = dr(0).ToString()
            End While

            sqlConn.Close()
        Catch ex As Exception

        End Try
        Return result
    End Function

    'get business name from businessID
    Public Function getBusinessNameBID(ByVal businessID As Integer) As String
        Dim result As String = ""
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "SELECT business.name FROM business WHERE (business_ID = @business_ID)"
            sqlCmd.Parameters.AddWithValue("@business_ID", businessID)
            dr = sqlCmd.ExecuteReader
            While dr.Read()
                result = dr(0).ToString()
            End While

            sqlConn.Close()
        Catch ex As Exception

        End Try
        Return result
    End Function

    'get businessID from outletID
    Public Function getBusinessID(ByVal outletID As Integer) As Integer
        Dim result As Integer
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "SELECT business_ID FROM outlet WHERE (outlet_ID = @outlet_ID)"
            sqlCmd.Parameters.AddWithValue("@outlet_ID", outletID)
            dr = sqlCmd.ExecuteReader
            While dr.Read()
                result = dr(0).ToString()
            End While

            sqlConn.Close()
        Catch ex As Exception

        End Try
        Return result
    End Function

    'get OutletID from businessID
    Public Function getOutletIDFromBusinessID(ByVal businessID As Integer) As Integer
        Dim result As Integer
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "SELECT outlet.outlet_ID FROM business INNER JOIN outlet ON business.business_ID = outlet.business_ID Where business.business_id = @business_ID"
            sqlCmd.Parameters.AddWithValue("@business_ID", businessID)
            dr = sqlCmd.ExecuteReader
            While dr.Read()
                result = dr(0).ToString()
            End While

            sqlConn.Close()
        Catch ex As Exception

        End Try
        Return result
    End Function
    'get outletID from comment
    Public Function getOutletIDFromComment(ByVal commentID As Integer) As Integer
        Dim result As Integer = 0
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "SELECT review.outlet_ID FROM comment INNER JOIN review ON comment.review_ID = review.review_ID Where comment.comment_ID = @commentID"
            sqlCmd.Parameters.AddWithValue("@commentID", commentID)
            dr = sqlCmd.ExecuteReader
            While dr.Read()
                result = dr(0).ToString()
            End While

            sqlConn.Close()
        Catch ex As Exception

        End Try
        Return result
    End Function
    'get outletID from review
    Public Function getOutletIDFromReview(ByVal reviewID As Integer) As Integer
        Dim result As Integer = 0
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "SELECT outlet_ID from review where review_ID = @reviewID"
            sqlCmd.Parameters.AddWithValue("@reviewID", reviewID)
            dr = sqlCmd.ExecuteReader
            While dr.Read()
                result = dr(0).ToString()
            End While

            sqlConn.Close()
        Catch ex As Exception

        End Try
        Return result
    End Function

    ''check business has at least one outlet
    'Public Function checkOutlets(ByVal business_ID As Integer)

    '    Dim result As Boolean = True
    '    Dim sqlConn As New SqlConnection()
    '    Dim sqlCmd As New SqlCommand()
    '    Dim dr As SqlDataReader
    '    Try
    '        sqlConn.ConnectionString = strConnectionID
    '        sqlConn.Open()
    '        sqlCmd.Connection = sqlConn
    '        'Query
    '        sqlCmd.CommandText = "select * from outlet where business_ID = @business_ID"

    '        'Assign parameters
    '        sqlCmd.Parameters.AddWithValue("@business_ID", business_ID)
    '        dr = sqlCmd.ExecuteReader
    '        'If there is rows returned, it means that the business has at least one outlet already exists
    '        If dr.HasRows Then
    '            result = False
    '        End If

    '        sqlConn.Close()
    '    Catch ex As Exception
    '        result = False
    '    End Try

    '    Return result
    '    'True = available
    '    'False = unavailable
    'End Function

#End Region

#Region "Search Module"

    'Update search tracker to keep track of searches if user is logged on
    Public Function trackSearch(ByVal searchField As String, ByVal userID As Integer)

        Dim result As Boolean = True

        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "Insert into search_tracker(date_searched, search_field, user_ID) Values (@dateSearched, @searchField, @userID )"

            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@dateSearched", DateTime.Now)
            sqlCmd.Parameters.AddWithValue("@searchfield", searchField)
            sqlCmd.Parameters.AddWithValue("@userID", userID)
            If sqlCmd.ExecuteNonQuery() > 0 Then
                result = True
            Else
                result = False
            End If
            sqlConn.Close()

        Catch ex As Exception
            result = False
        End Try

        'True = no errors
        'False = exceptions occured
        Return result

    End Function

    'Update search tracker to keep track of searches anonymously
    Public Function trackSearch(ByVal searchField As String)

        Dim result As Boolean = True

        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "Insert into search_tracker(date_searched, search_field) Values (@dateSearched, @searchField )"

            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@dateSearched", DateTime.Now)
            sqlCmd.Parameters.AddWithValue("@searchfield", searchField)
            If sqlCmd.ExecuteNonQuery() > 0 Then
                result = True
            Else
                result = False
            End If
            sqlConn.Close()

        Catch ex As Exception
            result = False
        End Try

        'True = no errors
        'False = exceptions occured
        Return result

    End Function

    'get the top 8 searched strings
    Public Function topSearch() As ArrayList
        Dim result As ArrayList = New ArrayList
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "SELECT distinct top 8 search_field, count(search_field) as numSearch from search_tracker group by search_field order by numSearch desc"
            dr = sqlCmd.ExecuteReader
            While dr.Read()
                'Add search field to arraylist
                Dim searchField = dr(0).ToString()
                result.Add(searchField)
            End While

            sqlConn.Close()
        Catch ex As Exception

        End Try
        Return result
    End Function

#End Region

#Region "Review and Rating module"

    'Add helpfulness rating to a review
    Public Function addReviewRate(ByVal reviewID As Integer, ByVal userID As Integer, ByVal helpful As Integer) As Boolean
        Dim result As Boolean = True

        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "Insert into review_rate(review_ID, user_ID, helpful) Values ( @review_ID, @user_ID, @helpful)"

            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@review_ID", reviewID)
            sqlCmd.Parameters.AddWithValue("@user_ID", userID)
            sqlCmd.Parameters.AddWithValue("@helpful", helpful)
            If sqlCmd.ExecuteNonQuery() > 0 Then
                result = True
            Else
                result = False
            End If

            sqlConn.Close()

        Catch ex As Exception
            'Exception occurs if violating primary key constraint if the same user tries to review for the same review twice
            result = False
        End Try

        'True = no errors
        'False = exceptions occured
        Return result

    End Function

    'Update review rating
    Public Function updateReviewRating(ByVal reviewID As Integer, ByVal helpfulness As Integer) As Boolean
        Dim result As Boolean = True

        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "Update review set helpfulness = @helpful where review_ID = @reviewID"

            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@helpful", helpfulness)
            sqlCmd.Parameters.AddWithValue("@reviewID", reviewID)
            If sqlCmd.ExecuteNonQuery() > 0 Then
                result = True
            Else
                result = False
            End If

            sqlConn.Close()

        Catch ex As Exception
            result = False
        End Try

        'True = no errors
        'False = exceptions occured
        Return result
    End Function

    'Add comment to a review
    Public Function addReviewComment(ByVal reviewID As Integer, ByVal userID As Integer, ByVal commentContent As String) As Boolean
        Dim result As Boolean = True

        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "Insert into comment(review_ID, user_ID, date_created, comment_content) Values ( @review_ID, @user_ID, @date_created, @comment_content)"

            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@review_ID", reviewID)
            sqlCmd.Parameters.AddWithValue("@user_ID", userID)
            sqlCmd.Parameters.AddWithValue("@date_created", DateTime.Now)
            sqlCmd.Parameters.AddWithValue("@comment_content", commentContent)
            If sqlCmd.ExecuteNonQuery() > 0 Then
                result = True
            Else
                result = False
            End If
            sqlConn.Close()

        Catch ex As Exception
            result = False
        End Try

        'True = no errors
        'False = exceptions occured
        Return result

    End Function

    'Get data to represent the overall review chart
    Public Function getChartData(ByVal outletID As Integer) As String()
        Dim result(3) As String

        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "SELECT AVG(review.rating_quality) AS Quality, AVG(review.rating_value) AS Value, AVG(review.rating_service) AS Service, AVG(review.rating_overall) AS Overall FROM outlet INNER JOIN review ON outlet.outlet_ID = review.outlet_ID AND outlet.outlet_ID = review.outlet_ID WHERE (outlet.outlet_ID = @outlet_ID)"
            sqlCmd.Parameters.AddWithValue("@outlet_ID", outletID)
            dr = sqlCmd.ExecuteReader
            While dr.Read()
                'Quality
                result(0) = Convert.ToInt32(dr(0).ToString())
                'Service
                result(1) = Convert.ToInt32(dr(1).ToString())
                'Value
                result(2) = Convert.ToInt32(dr(2).ToString())
                'Overall
                result(3) = Convert.ToDouble(dr(3).ToString())
            End While

            sqlConn.Close()
        Catch ex As Exception

        End Try
        Return result
    End Function

    'Get data to represent the rating distribution chart
    Public Function getDistriChartData(ByVal outletID As Integer) As String()
        Dim result(4) As String

        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "SELECT COUNT(rating_overall) AS countRating FROM review WHERE (outlet_ID = @outlet_ID) AND (rating_overall = @rating)"
            sqlCmd.Parameters.AddWithValue("@outlet_ID", outletID)
            sqlCmd.Parameters.Add(sqlCmd.CreateParameter).ParameterName = "@rating"

            For x = 1 To 5
                sqlCmd.Parameters.Item("@rating").Value = x
                dr = sqlCmd.ExecuteReader
                While dr.Read()
                    'Quality
                    result(x - 1) = Convert.ToInt32(dr(0).ToString())
                End While
                dr.Close()
            Next

            sqlConn.Close()

        Catch ex As Exception

        End Try
        Return result
    End Function

    'Get business and outlet names for page title
    Public Function getBusinessOutletName(ByVal outletID As Integer) As String
        Dim result As String = ""
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "SELECT business.name AS businessName, outlet.name AS outletName FROM outlet INNER JOIN business ON outlet.business_ID = business.business_ID WHERE (outlet.outlet_ID = @outlet_ID) GROUP BY business.name, outlet.name"
            sqlCmd.Parameters.AddWithValue("@outlet_ID", outletID)
            dr = sqlCmd.ExecuteReader
            While dr.Read()
                Dim businessName = dr(0).ToString()
                Dim outletName = dr(1).ToString()
                'Join the two names together
                result = businessName + " - " + outletName

            End While

            sqlConn.Close()
        Catch ex As Exception

        End Try
        Return result
    End Function

    'Get number of reviews belonging to an outlet
    Public Function getNumReviews(ByVal outletID As Integer) As Integer
        Dim result As Integer
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "SELECT COUNT(*) AS numReview FROM review WHERE (outlet_ID = @outletID) "
            sqlCmd.Parameters.AddWithValue("@outletID", outletID)
            result = Convert.ToInt32(sqlCmd.ExecuteScalar)
            sqlConn.Close()
        Catch ex As Exception

        End Try
        Return result
    End Function

    'Get overall ratings of an outlet based on all its reviews
    Public Function getOverallRating(ByVal outletID As Integer) As Double
        Dim result As Double
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Dim reviewCount = 0
        Dim totalRating = 0.0
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "SELECT review.rating_overall  FROM review INNER JOIN outlet ON review.outlet_ID = outlet.outlet_ID WHERE outlet.outlet_ID = @outlet_ID"
            sqlCmd.Parameters.AddWithValue("@outlet_ID", outletID)
            dr = sqlCmd.ExecuteReader
            While dr.Read()
                reviewCount = reviewCount + 1
                totalRating = totalRating + Convert.ToDouble(dr(0).ToString)
            End While
            result = totalRating / reviewCount
            sqlConn.Close()
        Catch ex As Exception
            Return -1
        End Try
        Return result
    End Function

    'Deletes a specified review
    Public Function deleteReview(ByVal reviewID As String) As Boolean
        Dim result = False
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "Delete from review_rate where (review_ID = @review_ID);Delete FROM comment WHERE (review_ID = @review_ID); Delete FROM review WHERE (review_ID = @review_ID)"
            sqlCmd.Parameters.AddWithValue("@review_ID", Convert.ToInt32(reviewID))

            If sqlCmd.ExecuteNonQuery() > 0 Then
                result = True
            End If
            sqlConn.Close()
        Catch ex As Exception
            result = False
        End Try
        Return result
    End Function

    'Deletes a specified review
    Public Function deleteComment(ByVal commentID As String) As Boolean
        Dim result = False
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "Delete FROM comment WHERE (comment_ID = @comment_ID) "
            sqlCmd.Parameters.AddWithValue("@comment_ID", Convert.ToInt32(commentID))

            If sqlCmd.ExecuteNonQuery() > 0 Then
                result = True
            End If
            sqlConn.Close()
        Catch ex As Exception
            result = False
        End Try
        Return result
    End Function

    'Add a new review
    Public Function addReview(ByVal outletID As Integer, ByVal userID As Integer, ByVal title As String, ByVal ratingQuality As Integer, ByVal ratingValue As Integer, ByVal ratingService As Integer, ByVal ratingOverall As Integer, ByVal ratingVerdict As String, ByVal reviewContent As String) As Boolean
        Dim result As Boolean = True

        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "Insert into review(outlet_ID, reviewer_ID, title, rating_quality, rating_value, rating_service, rating_overall, rating_verdict, helpfulness, date_created, review_content) Values(@outlet_ID, @reviewer_ID, @title, @rating_quality, @rating_value, @rating_service, @rating_overall, @rating_verdict, @helpfulness, @date_created, @review_content)"

            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@outlet_ID", outletID)
            sqlCmd.Parameters.AddWithValue("@reviewer_ID", userID)
            sqlCmd.Parameters.AddWithValue("@title", title)
            sqlCmd.Parameters.AddWithValue("@rating_quality", ratingQuality)
            sqlCmd.Parameters.AddWithValue("@rating_value", ratingValue)
            sqlCmd.Parameters.AddWithValue("@rating_service", ratingService)
            sqlCmd.Parameters.AddWithValue("@rating_overall", ratingOverall)
            sqlCmd.Parameters.AddWithValue("@rating_verdict", ratingVerdict)
            sqlCmd.Parameters.AddWithValue("@helpfulness", 0)
            sqlCmd.Parameters.AddWithValue("@date_created", DateTime.Now)
            sqlCmd.Parameters.AddWithValue("@review_content", reviewContent)

            If sqlCmd.ExecuteNonQuery() > 0 Then
                result = True
            Else
                result = False
            End If
            sqlConn.Close()

        Catch ex As Exception
            result = False
        End Try

        'True = no errors
        'False = exceptions occured
        Return result

    End Function

    'Get number of comments for a specific review
    Public Function getReviewNumComments(ByVal reviewID As Integer) As Integer
        Dim result As Integer = 0
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader

        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "SELECT COUNT(comment.comment_ID) AS commentNum FROM review INNER JOIN comment ON review.review_ID = comment.review_ID where review.review_ID = @reviewID"
            sqlCmd.Parameters.AddWithValue("@reviewID", reviewID)
            dr = sqlCmd.ExecuteReader
            While dr.Read()
                result = Convert.ToInt32(dr(0).ToString())
            End While
            sqlConn.Close()
        Catch ex As Exception

        End Try
        Return result
    End Function

    'Removes a report for a particular comment if it has been deleted
    Public Function removeReportComment(ByVal commentID As Integer) As Boolean
        Dim result = False
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "Delete from dashboard_report where type = @type and ID = @commentID"
            sqlCmd.Parameters.AddWithValue("@commentID", commentID)
            sqlCmd.Parameters.AddWithValue("@type", "report comment")

            If sqlCmd.ExecuteNonQuery() > 0 Then
                result = True
            End If
            sqlConn.Close()
        Catch ex As Exception
            result = False
        End Try
        Return result
    End Function

    'Removes a report for a particular comment if it has been deleted
    Public Function removeReportReview(ByVal reviewID As Integer) As Boolean
        Dim result = False
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "Delete from dashboard_report where type = @type and ID = @reviewID"
            sqlCmd.Parameters.AddWithValue("@reviewID", reviewID)
            sqlCmd.Parameters.AddWithValue("@type", "report review")

            If sqlCmd.ExecuteNonQuery() > 0 Then
                result = True
            End If
            sqlConn.Close()
        Catch ex As Exception
            result = False
        End Try
        Return result
    End Function

    'Retrieves report details using report ID
    Public Function retrieveReportDetails(ByVal reportID As Integer) As String()
        Dim result(3) As String

        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "SELECT type, comment, ID, resolved from dashboard_report where (report_ID = @reportID)"
            sqlCmd.Parameters.AddWithValue("@reportID", reportID)
            dr = sqlCmd.ExecuteReader
            While dr.Read()
                'Type
                result(0) = dr(0).ToString()
                'Comment
                result(1) = dr(1).ToString()
                'ID
                result(2) = Convert.ToInt32(dr(2).ToString())
                'Resolved
                result(3) = dr(3).ToString()
            End While

            sqlConn.Close()
        Catch ex As Exception

        End Try
        Return result
    End Function

    'Retrieves report details using business ID
    Public Function retrieveReportDetailsBID(ByVal ID As Integer) As String()
        Dim result(3) As String

        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "SELECT reportID ,type, comment, resolved from dashboard_report where (ID = @ID)"
            sqlCmd.Parameters.AddWithValue("@ID", ID)
            dr = sqlCmd.ExecuteReader
            While dr.Read()
                'ReportID
                result(0) = Convert.ToInt32(dr(0).ToString())
                'Type
                result(1) = dr(1).ToString()
                'Comment
                result(2) = dr(2).ToString()
                'Resolved
                result(3) = dr(3).ToString()
            End While

            sqlConn.Close()
        Catch ex As Exception

        End Try
        Return result
    End Function

    'Marks a report as resolved
    Public Sub resolveReport(ByVal reportID As Integer)

        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()

        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "update dashboard_report set resolved = @resolved where report_ID = @report_ID"

            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@resolved", 1)
            sqlCmd.Parameters.AddWithValue("@report_ID", reportID)
            sqlCmd.ExecuteNonQuery()
            'If there is rows returned, it means that login successful

        Catch ex As Exception

        End Try

    End Sub

    'Retrieves recommended outlets
    Public Function recommendOutlets(ByVal outletID As Integer) As ArrayList

        Dim finalOutlets As ArrayList = New ArrayList()
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn

            'Query
            sqlCmd.CommandText = "select  distinct top (3) outlet_ID, COUNT(outlet_ID), AVG( rating_overall ) from review where rating_overall > '2.50' and outlet_ID <> @outlet_ID and reviewer_ID IN ( select distinct reviewer_ID from review where rating_overall > '2.50' and outlet_ID = @outlet_ID ) group by outlet_Id order by COUNT(outlet_ID) desc, AVG(rating_overall) desc"
            sqlCmd.Parameters.AddWithValue("@outlet_ID", outletID)
            dr = sqlCmd.ExecuteReader

            While dr.Read
                finalOutlets.Add(dr(0))
            End While
            dr.Close()

            'If there is less than 3 recommended outlets, will take newest outlets instead
            Dim countOutletNew = 3 - finalOutlets.Count
            sqlCmd.CommandText = "select top (@num) outlet_ID from outlet where outlet_ID <> @outlet_ID order by date_created desc"
            sqlCmd.Parameters.AddWithValue("@num", countOutletNew)
            dr = sqlCmd.ExecuteReader

            While dr.Read
                finalOutlets.Add(dr(0))
            End While
            dr.Close()

        Catch ex As Exception

        End Try
        Return finalOutlets
    End Function

    'Get outletID of review
    Public Function getReviewOutlet(ByVal review_ID As Integer) As Integer
        Dim result As Integer
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "SELECT outlet_ID FROM review WHERE (review_ID = @review_ID) "
            sqlCmd.Parameters.AddWithValue("@review_ID", review_ID)
            dr = sqlCmd.ExecuteReader
            While dr.Read
                result = dr(0)
            End While
            sqlConn.Close()
        Catch ex As Exception

        End Try
        Return result
    End Function

    'Get number of reviews belonging to an outlet
    Public Function getReviewOverallRating(ByVal review_ID As Integer) As Integer
        Dim result As Integer
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "SELECT rating_overall FROM review WHERE (review_ID = @review_ID) "
            sqlCmd.Parameters.AddWithValue("@review_ID", review_ID)
            dr = sqlCmd.ExecuteReader
            While dr.Read
                result = dr(0)
            End While
            sqlConn.Close()
        Catch ex As Exception

        End Try
        Return result
    End Function

    'Check outlet exist
    Public Function checkOutletExist(ByVal outlet_ID As Integer) As Boolean
        Dim result As Boolean = False
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "SELECT * FROM outlet WHERE (outlet_ID = @outlet_ID) "
            sqlCmd.Parameters.AddWithValue("@outlet_ID", outlet_ID)
            dr = sqlCmd.ExecuteReader
            While dr.Read
                result = True
            End While
            sqlConn.Close()
        Catch ex As Exception
            result = False
        End Try
        Return result
    End Function

#End Region

#Region "Image module"

    'Add image
    Public Function addImage(ByVal imageName As String, ByVal image As HttpPostedFile, ByVal imageLength As Integer) As Integer

        Dim imageID As Integer = 0
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Try
            Dim imgByte(imageLength) As Byte
            image.InputStream.Read(imgByte, 0, imageLength)

            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "Insert into image(image_name, image, date_created) Values ( @imageName, @image, @date_created); SELECT SCOPE_IDENTITY()"

            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@imageName", imageName)
            sqlCmd.Parameters.AddWithValue("@image", imgByte)
            sqlCmd.Parameters.AddWithValue("@date_created", Date.Today.Date)

            imageID = sqlCmd.ExecuteScalar()

            sqlConn.Close()
        Catch ex As Exception

        End Try

        'True = no errors
        'False = exceptions occured
        Return imageID
    End Function

    'Check if banner image has been submitted, if not submitted, delete it
    Public Function checkImageBannerSubmit(ByVal imageID As Integer)

        Dim result As Boolean = False
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "select * from adv_image where adv_image_ID = @imageID and status = @status"

            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@imageID", imageID)
            sqlCmd.Parameters.AddWithValue("@status", "initializing")

            dr = sqlCmd.ExecuteReader

            'If there is rows returned, it means that login successful
            If dr.HasRows Then
                result = True

            End If


            If result = True Then

            End If
            sqlConn.Close()

        Catch ex As Exception

        End Try
        Return result
    End Function

    'Delete imageBanners which were not submitted 
    Public Sub deleteInitialImageBanner(ByVal userID As Integer)
        Dim result As Boolean = False
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()

        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query

            sqlCmd.CommandText = "delete from adv_image where user_ID = @userID and status = @status"

            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@userID", userID)
            sqlCmd.Parameters.AddWithValue("@status", "initializing")
            sqlCmd.ExecuteNonQuery()

            sqlConn.Close()
        Catch ex As Exception
        End Try
    End Sub

    'Delete imageBanner
    Public Sub deleteImageBanner(ByVal imageID As Integer)
        Dim result As Boolean = False
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()

        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query

            sqlCmd.CommandText = "delete from adv_image where adv_image_ID = @imageID "

            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@imageID", imageID)


            sqlCmd.ExecuteNonQuery()

            sqlConn.Close()
        Catch ex As Exception
        End Try
    End Sub

    'Delete Image
    Public Sub deleteImage(ByVal imageID As Integer)
        Dim result As Boolean = False
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()

        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query

            sqlCmd.CommandText = "delete from image where image_ID = @imageID "

            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@imageID", imageID)
            sqlCmd.ExecuteNonQuery()

            sqlConn.Close()
        Catch ex As Exception
        End Try
    End Sub

    'Update User Image
    Public Function updateImage(ByVal imageID As Integer, ByVal imageName As String, ByVal image As HttpPostedFile, ByVal imageLength As Integer) As Integer
        Dim result As Boolean = True
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Try
            Dim imgByte(imageLength) As Byte
            image.InputStream.Read(imgByte, 0, imageLength)
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "Update image set image_name = @imageName, image = @image, date_created = @dateCreated Where image_ID = @imageID"

            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@imageID", imageID)
            sqlCmd.Parameters.AddWithValue("@imageName", imageName)
            sqlCmd.Parameters.AddWithValue("@image", imgByte)
            sqlCmd.Parameters.AddWithValue("@dateCreated", Date.Today.Date)

            sqlCmd.ExecuteNonQuery()
            sqlConn.Close()
        Catch ex As Exception
            result = False
        End Try

        'True = no errors
        'False = exceptions occured
        Return result
    End Function

    'Updates imageBanner with startdate, enddate and status
    Public Sub updateImageBanner(ByVal imageID As Integer, ByVal startDate As Date, ByVal endDate As Date, ByVal cost As Integer, ByVal outletID As Integer)

        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Try

            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "Update adv_image set start_date = @start_date, end_date = @end_date, status = @status, cost = @cost, outlet_ID = @outletID where adv_image_ID = @imageID"
            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@imageID", imageID)
            sqlCmd.Parameters.AddWithValue("@status", "initialized")
            sqlCmd.Parameters.AddWithValue("@start_date", startDate)
            sqlCmd.Parameters.AddWithValue("@end_date", endDate)
            sqlCmd.Parameters.AddWithValue("@cost", cost)
            sqlCmd.Parameters.AddWithValue("@outletID", outletID)

            sqlCmd.ExecuteNonQuery()
            sqlConn.Close()
        Catch ex As Exception

        End Try

    End Sub

    'Update image banner with new image by adding new temp image
    Public Function updateBannerImageTemp(ByVal imageName As String, ByVal image As HttpPostedFile, ByVal imageLength As Integer, ByVal userID As Integer) As Integer

        Dim imageID As Integer = 0
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Try
            Dim imgByte(imageLength) As Byte
            image.InputStream.Read(imgByte, 0, imageLength)

            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "Insert into adv_image(image_name, image, status, date_created,user_ID) Values ( @imageName, @image, @status, @date_created, @userID); SELECT SCOPE_IDENTITY()"

            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@imageName", imageName)
            sqlCmd.Parameters.AddWithValue("@image", imgByte)
            sqlCmd.Parameters.AddWithValue("@status", "updateTemp")
            sqlCmd.Parameters.AddWithValue("@date_created", DateTime.Now)
            sqlCmd.Parameters.AddWithValue("@userID", userID)

            imageID = sqlCmd.ExecuteScalar()

            sqlConn.Close()
        Catch ex As Exception

        End Try

        'True = no errors
        'False = exceptions occured
        Return imageID
    End Function

    'Update image banner with new image by assigning new image from temp image
    Public Sub updateBannerImageChange(ByVal originalImageID As Integer, ByVal newImageID As Integer)

        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn

            'First query to update the old image
            sqlCmd.CommandText = "Update adv_image set image_name = (Select image_name from adv_image where adv_image_ID = @newImageID), image = (select image from adv_image where adv_image_ID = @newImageID ) where adv_image_ID = @oldImageID"
            sqlCmd.Parameters.AddWithValue("@newImageID", newImageID)
            sqlCmd.Parameters.AddWithValue("@oldImageID", originalImageID)
            sqlCmd.ExecuteNonQuery()

            'Second query to delete the temp image
            sqlCmd.CommandText = "delete from adv_image where adv_image_ID = @newImageID"
            sqlCmd.ExecuteNonQuery()

            sqlConn.Close()
        Catch ex As Exception

        End Try
    End Sub

    'Add image banner temporarily
    Public Function addImageBanner(ByVal imageName As String, ByVal image As HttpPostedFile, ByVal imageLength As Integer, ByVal userID As Integer) As Integer

        Dim imageID As Integer = 0
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Try
            Dim imgByte(imageLength) As Byte
            image.InputStream.Read(imgByte, 0, imageLength)

            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "Insert into adv_image(image_name, image, status, date_created,user_ID) Values ( @imageName, @image, @status, @date_created, @userID); SELECT SCOPE_IDENTITY()"

            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@imageName", imageName)
            sqlCmd.Parameters.AddWithValue("@image", imgByte)
            sqlCmd.Parameters.AddWithValue("@status", "initializing")
            sqlCmd.Parameters.AddWithValue("@date_created", DateTime.Now)
            sqlCmd.Parameters.AddWithValue("@userID", userID)

            imageID = sqlCmd.ExecuteScalar()

            sqlConn.Close()
        Catch ex As Exception

        End Try

        'True = no errors
        'False = exceptions occured
        Return imageID
    End Function

    'Change banner status from initializting to payment pending
    Public Sub updateImageBannerUnconfirmed(ByVal imageID As Integer)
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Try

            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "Update adv_image set status = @status where adv_image_ID = @imageID"
            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@imageID", imageID)
            sqlCmd.Parameters.AddWithValue("@status", "payment pending")


            sqlCmd.ExecuteNonQuery()
            sqlConn.Close()
        Catch ex As Exception

        End Try
    End Sub

    'Assign businessID to image
    Public Function assignImageBusiness(ByVal imageID As Integer, ByVal businessID As Integer)
        Dim result As Boolean = True
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "Update image set business_ID = @businessID where image_ID = @imageID"
            sqlCmd.Parameters.AddWithValue("@businessID", businessID)
            sqlCmd.Parameters.AddWithValue("@imageID", imageID)
            If sqlCmd.ExecuteNonQuery() > 0 Then
                result = True
            End If
            sqlConn.Close()
        Catch ex As Exception
            result = False
        End Try
        Return result
    End Function

    'Assign userID to image
    Public Function assignUserImage(ByVal userID As Integer, ByVal imageID As Integer)
        Dim result As Boolean = True
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "Update image set user_ID = @userID where image_ID = @imageID"
            sqlCmd.Parameters.AddWithValue("@userID", userID)
            sqlCmd.Parameters.AddWithValue("@imageID", imageID)
            If sqlCmd.ExecuteNonQuery() > 0 Then
                result = True
            End If
            sqlConn.Close()
        Catch ex As Exception
            result = False
        End Try
        Return result
    End Function

    'Get imageID of business
    Public Function getBusinessImage(ByVal businessID As Integer) As Integer
        Dim imageID As Integer
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Dim reviewCount = 0
        Dim totalRating = 0.0
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "SELECT  image_ID from image where business_ID = @businessID"
            sqlCmd.Parameters.AddWithValue("@businessID", businessID)
            dr = sqlCmd.ExecuteReader
            While dr.Read()
                imageID = dr(0)
            End While
        Catch ex As Exception
            Return Nothing
        End Try
        Return imageID
    End Function

    'Get imageID of outlet
    Public Function getOutletImage(ByVal outletID As Integer) As Integer
        Dim imageID As Integer
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader

        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "SELECT image_ID from image where business_ID = ( Select business_ID from outlet where outlet_ID = @outletID)"
            sqlCmd.Parameters.AddWithValue("@outletID", outletID)
            dr = sqlCmd.ExecuteReader
            While dr.Read()
                imageID = dr(0)
            End While
        Catch ex As Exception
            Return Nothing
        End Try
        Return imageID
    End Function

    'Get imageID of user profile
    Public Function getUserImage(ByVal userID As Integer) As Integer
        Dim image_ID As Integer = 0
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "SELECT image_ID from image where user_ID = ( Select user_ID from user_account where user_ID = @userID)"
            sqlCmd.Parameters.AddWithValue("@userID", userID)
            dr = sqlCmd.ExecuteReader
            While dr.Read()
                image_ID = dr(0)
            End While
            sqlConn.Close()
        Catch ex As Exception
            Return 0
        End Try
        Return image_ID
    End Function

    'Get imageID of top 3 most popular businesses
    Public Function getTopPopularBusiness() As ArrayList
        Dim result As ArrayList = New ArrayList
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "SELECT TOP (3) image.image_ID, business.visits FROM business INNER JOIN image ON business.business_ID = image.business_ID ORDER BY business.visits DESC"
            dr = sqlCmd.ExecuteReader
            While dr.Read()
                'Add search field to arraylist
                Dim imageID = dr(0).ToString()
                result.Add(imageID)
            End While

            sqlConn.Close()
        Catch ex As Exception

        End Try
        Return result
    End Function

    'Get imageID of top 3 newest businesses
    Public Function getTopNewestBusiness() As ArrayList
        Dim result As ArrayList = New ArrayList
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "SELECT TOP (3) image.image_ID FROM business INNER JOIN image ON business.business_ID = image.business_ID ORDER BY business.date_created DESC"
            dr = sqlCmd.ExecuteReader
            While dr.Read()
                'Add search field to arraylist
                Dim imageID = dr(0).ToString()
                result.Add(imageID)
            End While

            sqlConn.Close()
        Catch ex As Exception

        End Try
        Return result
    End Function

    'Get imageID of top businesses for each category
    Public Function getTopRatedBusinessByCategory(ByVal category As String) As ArrayList
        Dim result As ArrayList = New ArrayList
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            'getting the top 1 for each category
            sqlCmd.CommandText = "SELECT TOP (1) image.image_ID, review.rating_overall FROM outlet INNER JOIN business ON outlet.business_ID = business.business_ID INNER JOIN review ON outlet.outlet_ID = review.outlet_ID AND outlet.outlet_ID = review.outlet_ID INNER JOIN image ON business.business_ID = image.business_ID WHERE (business.category = @category) ORDER BY review.rating_overall DESC"
            sqlCmd.Parameters.AddWithValue("@category", category)
            dr = sqlCmd.ExecuteReader
            While dr.Read()
                Dim imageID = dr(0).ToString()
                result.Add(imageID)
            End While

            sqlConn.Close()
        Catch ex As Exception

        End Try
        Return result
    End Function

    'get top 5 for each category
    Public Function getTop5PopularByCategory(ByVal category As String) As ArrayList
        Dim result As ArrayList = New ArrayList
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "SELECT TOP (5) image.image_ID, business.visits FROM business INNER JOIN image ON business.business_ID = image.business_ID WHERE (business.category = @category) ORDER BY business.visits DESC"
            sqlCmd.Parameters.AddWithValue("@category", category)
            dr = sqlCmd.ExecuteReader
            While dr.Read()
                'Add search field to arraylist
                Dim imageID = dr(0).ToString()
                result.Add(imageID)
            End While

            sqlConn.Close()
        Catch ex As Exception

        End Try
        Return result
    End Function
    'Get imageID of outlet
    Public Function getOutletImageID(ByVal imageID As Integer) As Integer
        Dim result As Integer
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "SELECT DISTINCT outlet.outlet_ID FROM business INNER JOIN outlet ON business.business_ID = outlet.business_ID INNER JOIN image ON business.business_ID = image.business_ID WHERE (image.image_ID = @imageID)"
            sqlCmd.Parameters.AddWithValue("@imageID", imageID)
            dr = sqlCmd.ExecuteReader
            While dr.Read()
                'Add search field to arraylist
                Dim outletID = Convert.ToInt32(dr(0).ToString())
                result = outletID
            End While

            sqlConn.Close()
        Catch ex As Exception

        End Try
        Return result
    End Function
    'Get imageID of business
    Public Function getBusinessImageID(ByVal imageID As Integer) As Integer
        Dim result As Integer
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "SELECT DISTINCT business.business_ID FROM business INNER JOIN image ON business.business_ID = image.business_ID WHERE (image.image_ID = @imageID)"
            sqlCmd.Parameters.AddWithValue("@imageID", imageID)
            dr = sqlCmd.ExecuteReader
            While dr.Read()
                'Add search field to arraylist
                Dim businessID = Convert.ToInt32(dr(0).ToString())
                result = businessID
            End While

            sqlConn.Close()
        Catch ex As Exception

        End Try
        Return result
    End Function
    'get outletName by Image ID 
    Public Function getOutletNameImageID(ByVal imageID As Integer) As String
        Dim result As Integer
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "SELECT DISTINCT outlet.name FROM business INNER JOIN outlet ON business.business_ID = outlet.business_ID INNER JOIN image ON business.business_ID = image.business_ID WHERE (image.image_ID = @imageID)"
            sqlCmd.Parameters.AddWithValue("@imageID", imageID)
            dr = sqlCmd.ExecuteReader
            While dr.Read()
                'Add search field to arraylist
                Dim name = dr(0).ToString()
                result = name
            End While

            sqlConn.Close()
        Catch ex As Exception

        End Try
        Return result
    End Function
    'get imageID of each sub-category
    Public Function getSubCategoryImageID(ByVal category As String) As ArrayList
        Dim result As ArrayList = New ArrayList
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            'getting the top 1 for each category
            sqlCmd.CommandText = "SELECT image.image_ID FROM outlet INNER JOIN business ON outlet.business_ID = business.business_ID INNER JOIN image ON business.business_ID = image.business_ID WHERE (business.category = @category) ORDER BY outlet.date_created DESC"
            sqlCmd.Parameters.AddWithValue("@category", category)
            dr = sqlCmd.ExecuteReader
            While dr.Read()
                Dim imageID = dr(0).ToString()
                result.Add(imageID)
            End While

            sqlConn.Close()
        Catch ex As Exception

        End Try
        Return result
    End Function


#End Region

#Region "Advertising"

    'Delete previous payment items
    Public Sub deletePaymentItems(ByVal userID As Integer)

        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Try

            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "delete from payment where user_ID = @user_ID"

            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@user_ID", userID)
            sqlCmd.ExecuteNonQuery()
            sqlConn.Close()
        Catch ex As Exception
        End Try
    End Sub


    'Insert payment item for advertisement
    Public Function insertPaymentItem(ByVal userID As Integer, ByVal name As String, ByVal cost As Integer, ByVal type As String, ByVal adv_ID As Integer)

        Dim itemID As Integer = 0
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Try

            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "Insert into payment(user_ID, name, cost, type, adv_ID ) Values ( @user_ID, @name, @cost, @type, @adv_ID); SELECT SCOPE_IDENTITY()"

            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@user_ID", userID)
            sqlCmd.Parameters.AddWithValue("@name", name)
            sqlCmd.Parameters.AddWithValue("@cost", cost)
            sqlCmd.Parameters.AddWithValue("@type", type)
            sqlCmd.Parameters.AddWithValue("@adv_ID", adv_ID)

            itemID = sqlCmd.ExecuteScalar()

            sqlConn.Close()
        Catch ex As Exception

        End Try

        'True = no errors
        'False = exceptions occured
        Return itemID
    End Function

    'Increment impressions for SEO
    Public Sub incrementImpressionSEO(ByVal outlet_ID As Integer)
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Dim oldImpression As Integer
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query to retrieve old impression number 
            sqlCmd.CommandText = "select impressions from adv_SEO where outlet_ID = @outlet_ID"

            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@outlet_ID", outlet_ID)
            dr = sqlCmd.ExecuteReader

            'If there is rows returned, it means that login successful
            While dr.Read()
                oldImpression = dr(0)
            End While

            dr.Close()
            'Second Query
            sqlCmd.CommandText = "update adv_SEO set impressions = @newImpression where outlet_ID = @outlet_ID"
            sqlCmd.Parameters.AddWithValue("@newImpression", oldImpression + 1)
            sqlCmd.ExecuteNonQuery()

            sqlConn.Close()
        Catch ex As Exception
        End Try
    End Sub


    'Increment clicks for banner 
    Public Sub incrementClicksSEO(ByVal outlet_ID)
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Dim oldClicks As Integer
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query to retrieve old impression number 
            sqlCmd.CommandText = "select clicks from adv_SEO where outlet_ID = @outlet_ID"

            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@outlet_ID", outlet_ID)
            dr = sqlCmd.ExecuteReader

            'If there is rows returned, it means that login successful
            While dr.Read()
                oldClicks = dr(0)
            End While

            dr.Close()
            'Second Query
            sqlCmd.CommandText = "update adv_SEO set clicks = @newClicks where outlet_ID = @outlet_ID"
            sqlCmd.Parameters.AddWithValue("@newClicks", oldClicks + 1)
            sqlCmd.ExecuteNonQuery()

            sqlConn.Close()
        Catch ex As Exception
        End Try

    End Sub

    'Increment impressions for banner 
    Public Sub incrementImpression(ByVal imageID)
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Dim oldImpression As Integer
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query to retrieve old impression number 
            sqlCmd.CommandText = "select impressions from adv_image where adv_image_ID = @imageID"

            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@imageID", imageID)
            dr = sqlCmd.ExecuteReader

            'If there is rows returned, it means that login successful
            While dr.Read()
                oldImpression = dr(0)
            End While

            dr.Close()
            'Second Query
            sqlCmd.CommandText = "update adv_image set impressions = @newImpression where adv_image_ID = @imageID"
            sqlCmd.Parameters.AddWithValue("@newImpression", oldImpression + 1)
            sqlCmd.ExecuteNonQuery()

            sqlConn.Close()
        Catch ex As Exception
        End Try

    End Sub

    'Increment clicks for banner 
    Public Sub incrementClicks(ByVal imageID)
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Dim oldClicks As Integer
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query to retrieve old impression number 
            sqlCmd.CommandText = "select clicks from adv_image where adv_image_ID = @imageID"

            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@imageID", imageID)
            dr = sqlCmd.ExecuteReader

            'If there is rows returned, it means that login successful
            While dr.Read()
                oldClicks = dr(0)
            End While

            dr.Close()
            'Second Query
            sqlCmd.CommandText = "update adv_image set clicks = @newClicks where adv_image_ID = @imageID"
            sqlCmd.Parameters.AddWithValue("@newClicks", oldClicks + 1)
            sqlCmd.ExecuteNonQuery()

            sqlConn.Close()
        Catch ex As Exception
        End Try

    End Sub

    'Get number of active and pending SEO and banner advertisements
    Public Function getActivePendingAdvertisements(ByVal user_ID As Integer)
        Dim result As ArrayList = New ArrayList()
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn

            'Query to retrieve number of pending SEO
            sqlCmd.CommandText = "select count(adv_SEO_ID) from adv_SEO where user_ID = @user_ID and status <> 'live'"

            sqlCmd.Parameters.AddWithValue("@user_ID", user_ID)
            dr = sqlCmd.ExecuteReader
            While dr.Read()
                result.Add(dr(0))
            End While
            dr.Close()

            'Second Query to retrieve number of active SEO
            sqlCmd.CommandText = "select count(adv_SEO_ID) from adv_SEO where user_ID = @user_ID and status = 'live'"
            dr = sqlCmd.ExecuteReader
            While dr.Read()
                result.Add(dr(0))
            End While
            dr.Close()

            'Third Query to retrieve number of banners
            sqlCmd.CommandText = "select count(adv_image_ID) from adv_image where user_ID = @user_ID and status <> 'live'"

            dr = sqlCmd.ExecuteReader
            While dr.Read()
                result.Add(dr(0))
            End While
            dr.Close()

            'Fourth Query to retrieve number of banners
            sqlCmd.CommandText = "select count(adv_image_ID) from adv_image where user_ID = @user_ID and status = 'live'"
            dr = sqlCmd.ExecuteReader
            While dr.Read()
                result.Add(dr(0))
            End While
            dr.Close()


            sqlConn.Close()
        Catch ex As Exception

        End Try
        Return result

    End Function

    'Get outletID of a banner, for redirecting.
    Public Function getOutletIDBanner(ByVal imageID)
        Dim result As Integer = 0
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query to retrieve old impression number 
            sqlCmd.CommandText = "select outlet_ID from adv_image where adv_image_ID = @imageID"

            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@imageID", imageID)
            dr = sqlCmd.ExecuteReader

            'If there is rows returned, it means that login successful
            While dr.Read()
                result = dr(0)
            End While

            dr.Close()
            'Second Query
            sqlConn.Close()
        Catch ex As Exception
            result = 0
        End Try
        Return result

    End Function

    'Select select banner images for front page banner.
    Public Function getFrontPageBanner() As ArrayList

        Dim result As ArrayList = New ArrayList
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Dim count As Integer = 0
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "select top 7 adv_image_ID from adv_image where status = @status and start_date <= @today and end_date >= @today order by impressions asc "

            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@status", "live")
            sqlCmd.Parameters.AddWithValue("@today", DateTime.Today.Date)

            dr = sqlCmd.ExecuteReader

            'If there is rows returned, it means that login successful
            While dr.Read()
                result.Add(count)
                result.Add(dr(0))
                count = count + 1
            End While

            sqlConn.Close()
        Catch ex As Exception
            result = Nothing
        End Try

        'True = no errors
        'False = exceptions occured
        Return result
    End Function

    'Delete an item from SEO
    Public Sub deleteSEOItem(ByVal adv_SEO_ID As Integer)

        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "Delete from adv_SEO where adv_SEO_ID= @adv_SEO_ID"
            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@adv_SEO_ID", adv_SEO_ID)
            sqlCmd.ExecuteNonQuery()
            sqlConn.Close()
        Catch ex As Exception


        End Try
    End Sub

    'Add selected item to temporary database 
    Public Function addTempItem(ByVal userID As Integer, ByVal name As String, ByVal outletID As Integer) As Integer

        Dim itemID As Integer = 0
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Try

            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "Insert into temp_adv(user_ID, created, name, outlet_ID) Values ( @user_ID, @created, @name, @outlet_ID); SELECT SCOPE_IDENTITY()"

            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@user_ID", userID)
            sqlCmd.Parameters.AddWithValue("@created", DateTime.Now)
            sqlCmd.Parameters.AddWithValue("@name", name)
            sqlCmd.Parameters.AddWithValue("@outlet_ID", outletID)

            itemID = sqlCmd.ExecuteScalar()

            sqlConn.Close()
        Catch ex As Exception

        End Try

        'True = no errors
        'False = exceptions occured
        Return itemID
    End Function

    'Check if item has aready been selected
    Public Function checkTempItemExists(ByVal userID As Integer, ByVal outletID As Integer) As Boolean
        Dim result As Boolean = True
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "select * from temp_adv where user_ID = @userID and outlet_ID = @outletID"

            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@userID", userID)
            sqlCmd.Parameters.AddWithValue("@outletID", outletID)

            dr = sqlCmd.ExecuteReader

            'If there is rows returned, it means that login successful
            If dr.HasRows Then
                result = True
            Else
                result = False
            End If

            sqlConn.Close()
        Catch ex As Exception
            result = False
        End Try

        'True = no errors
        'False = exceptions occured
        Return result
    End Function

    'Check if item has aready been added as a pending SEO
    Public Function checkAdvItemExists(ByVal userID As Integer, ByVal outletID As Integer) As Boolean
        Dim result As Boolean = True
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "select * from adv_SEO where user_ID = @userID and outlet_ID = @outletID"

            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@userID", userID)
            sqlCmd.Parameters.AddWithValue("@outletID", outletID)

            dr = sqlCmd.ExecuteReader

            'If there is rows returned, it means that login successful
            If dr.HasRows Then
                result = True
            Else
                result = False
            End If

            sqlConn.Close()
        Catch ex As Exception
            result = False
        End Try

        'True = no errors
        'False = exceptions occured
        Return result
    End Function

    'Add a item to SEO table 
    Public Sub addSEOItem(ByVal userID As Integer, ByVal name As String, ByVal outletID As Integer, ByVal startDate As Date, ByVal endDate As Date, ByVal duration As String)

        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Try

            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "Insert into adv_SEO(user_ID, created, name, outlet_ID,start_date, end_date, duration, payment, status, impressions, clicks ) Values ( @user_ID, @created, @name, @outlet_ID, @start_date, @end_date, @duration, @payment, @status, @impressions, @clicks)"

            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@user_ID", userID)
            sqlCmd.Parameters.AddWithValue("@created", DateTime.Now)
            sqlCmd.Parameters.AddWithValue("@name", name)
            sqlCmd.Parameters.AddWithValue("@outlet_ID", outletID)
            sqlCmd.Parameters.AddWithValue("@start_date", startDate)
            sqlCmd.Parameters.AddWithValue("@end_date", endDate)
            sqlCmd.Parameters.AddWithValue("@duration", duration)
            sqlCmd.Parameters.AddWithValue("@payment", "false")
            sqlCmd.Parameters.AddWithValue("@status", "payment pending")
            sqlCmd.Parameters.AddWithValue("@impressions", 0)
            sqlCmd.Parameters.AddWithValue("@clicks", 0)
            sqlCmd.ExecuteNonQuery()

            sqlConn.Close()
        Catch ex As Exception

        End Try

        'True = no errors
        'False = exceptions occured

    End Sub

    'Update a SEO item 
    Public Sub updateSEOItem(ByVal adv_SEO_ID As Integer, ByVal duration As String, ByVal startDate As Date, ByVal endDate As Date)
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Try

            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "Update adv_SEO set duration = @duration, start_date = @startDate, end_date = @endDate where adv_SEO_ID = @advSEOID"

            'Assign parameters

            sqlCmd.Parameters.AddWithValue("@startDate", startDate)
            sqlCmd.Parameters.AddWithValue("@endDate", endDate)
            sqlCmd.Parameters.AddWithValue("@duration", duration)
            sqlCmd.Parameters.AddWithValue("@advSEOID", adv_SEO_ID)

            sqlCmd.ExecuteNonQuery()

            sqlConn.Close()
        Catch ex As Exception

        End Try

        'True = no errors
        'False = exceptions occured
    End Sub

    'Delete all SEO which are unconfirmed
    Public Sub clearSEO(ByVal userID As Integer)


        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "Delete from adv_SEO where user_ID= @user_ID and status=@status"
            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@user_ID", userID)
            sqlCmd.Parameters.AddWithValue("@status", "unconfirmed")
            sqlCmd.ExecuteNonQuery()

            sqlConn.Close()
        Catch ex As Exception


        End Try

    End Sub

    'Delete all temp SEO
    Public Sub clearSEOTemp(ByVal userID As Integer)


        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "Delete from temp_adv where user_ID= @user_ID"
            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@user_ID", userID)
            sqlCmd.ExecuteNonQuery()

            sqlConn.Close()
        Catch ex As Exception


        End Try

    End Sub

    'Delete specific temp SEO 
    Public Sub deleteTempItem(ByVal temp_adv_ID As Integer)

        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "Delete from temp_adv where temp_adv_ID= @temp_adv_ID"
            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@temp_adv_ID", temp_adv_ID)
            sqlCmd.ExecuteNonQuery()
            sqlConn.Close()
        Catch ex As Exception


        End Try
    End Sub

    'Check if outlet is under SEO
    Public Function checkSEO(ByVal outlet_ID As Integer)
        Dim result As Boolean = True
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "select * from adv_SEO where outlet_ID = @outlet_ID and status = 'live' and start_date <= @today and end_date >= @today"

            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@outlet_ID", outlet_ID)
            sqlCmd.Parameters.AddWithValue("@today", DateTime.Today.Date)
            dr = sqlCmd.ExecuteReader

            'If there is rows returned, it means that it is under SEO
            If dr.HasRows Then
                result = True
            Else
                result = False
            End If

            sqlConn.Close()
        Catch ex As Exception
            result = False
        End Try

        'True = no errors
        'False = exceptions occured
        Return result
    End Function

#End Region

#Region "Follow Shop"
    ' Add the selected outlet
    Public Function addFollowShop(ByVal outlet_ID As Integer, ByVal user_ID As Integer, ByVal datestarted As Date) As Integer
        Dim follow_ID As Integer = 0
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "Insert into follow_shop(outlet_ID ,user_ID, datestarted) Values (@outlet_ID ,@user_ID, @datestarted);"

            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@outlet_ID", outlet_ID)
            sqlCmd.Parameters.AddWithValue("@user_ID", user_ID)
            sqlCmd.Parameters.AddWithValue("@datestarted", Today.Date)
            follow_ID = sqlCmd.ExecuteScalar()
            sqlConn.Close()
        Catch ex As Exception
        End Try
        'True = no errors
        'False = exceptions occured
        Return follow_ID
    End Function

    'Retrieve follow shops details
    Public Function retrieveFollowShop(ByVal outlet_ID As Integer)
        Dim result = New ArrayList()
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "select unique count( * ) from reviews where outlet_ID = @outlet_ID and date > getdate() and date >=dateadd(d, -7, getdate())"
            'Assign parameters
            sqlCmd.Parameters.AddWithValue("@outlet_ID", outlet_ID)
            dr = sqlCmd.ExecuteReader
            While dr.Read()
                result(0) = dr(0)
            End While
            sqlConn.Close()
        Catch ex As Exception
        End Try
        'True = no errors
        'False = exceptions occured
        Return result
    End Function

    'delete follow shops details 
    Public Function deleteFollowShop(ByVal outlet_ID As Integer, ByVal user_ID As Integer) As Boolean
        Dim result = False
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "Delete FROM follow_shop WHERE (outlet_ID = @outlet_ID) AND (user_ID=@user_ID)"
            sqlCmd.Parameters.AddWithValue("@outlet_ID", Convert.ToInt32(outlet_ID))
            sqlCmd.Parameters.AddWithValue("@user_ID", Convert.ToInt32(user_ID))

            If sqlCmd.ExecuteNonQuery() > 0 Then
                result = True
            End If
            sqlConn.Close()
        Catch ex As Exception
            result = False
        End Try
        Return result
    End Function

    ' check if there is any follow shop
    Public Function checkFollowShop(ByVal outlet_ID As Integer, ByVal user_ID As Integer)

        Dim result As Boolean = True
        Dim sqlConn As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim dr As SqlDataReader
        Try
            sqlConn.ConnectionString = strConnectionID
            sqlConn.Open()
            sqlCmd.Connection = sqlConn
            'Query
            sqlCmd.CommandText = "SELECT * FROM follow_shop  WHERE (user_ID = @user_ID) AND (outlet_ID = @outlet_ID)"

            'Assign parameters 
            sqlCmd.Parameters.AddWithValue("@user_ID", user_ID)
            sqlCmd.Parameters.AddWithValue("@outlet_ID", outlet_ID)

            dr = sqlCmd.ExecuteReader
            'If there is rows returned, it means that the follow shop already exists
            If dr.HasRows Then
                result = False
            End If

            sqlConn.Close()
        Catch ex As Exception
            result = False
        End Try

        Return result
        'True = available
        'False = unavailable
    End Function

#End Region

End Class
